package flst.ipiweb.persistance.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import flst.ipiweb.persistance.dao.IStatistiqueDao;
import flst.ipiweb.service.ServiceDate;
import flst.ipiweb.transverse.IPIException;
import flst.ipiweb.transverse.Statistique;

public class StatistiqueDaoJdbc implements IStatistiqueDao{
	
	private JdbcUtils jdbcUtils = JdbcUtils.getInstance();

	@Override
	public List<Statistique> ArticleCommandeByMonth(int refArticle) throws IPIException{
		List<Statistique> listeStats = new ArrayList<Statistique>();

		try {
			Connection vConnection = jdbcUtils.getConnection();
			Statement vStmt = vConnection.createStatement();

			String vRequeteSelect = "SELECT SUM(QUANTITY) as NOMBRE, CREATION_DATE FROM IPII_ORDER_LINES line, IPII_ORDERS o WHERE PRODUCT_NUMBER = ? AND line.ORDER_ID = o.ORDER_ID AND CREATION_DATE BETWEEN ? AND ? GROUP BY CREATION_DATE ORDER BY CREATION_DATE";
			PreparedStatement vPs = vConnection.prepareStatement(vRequeteSelect);

			// par refArticle
			vPs.setInt(1, refArticle);
			
			ServiceDate sDate = new ServiceDate();
			Date dateDebut = sDate.getFirstDateOfMonth();
			vPs.setDate(2, sDate.convertDateToSqlDate(dateDebut));
			Date dateFin = sDate.getLastDateOfMonth();
			vPs.setDate(3, sDate.convertDateToSqlDate(dateFin));
			vPs.execute();
			
			ResultSet vRs = vPs.getResultSet();
			while (vRs.next()) {
				Statistique stat = new Statistique();
				// Date
				
				Date laDate = sDate.convertSqlDateToDate(vRs.getDate("CREATION_DATE"));
				stat.setDate(sDate.convertDateToString(laDate));
				stat.setNombre(vRs.getInt("NOMBRE"));

				listeStats.add(stat);
			}

			vStmt.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("Exception.dao.technique");
		}
		return listeStats;
	}
	
	@Override
	public List<Statistique> ArticleCommandeByYear(int refArticle){
		// TODO Auto-generated method stub
		return null;
	}
	
	@Override
	public List<Statistique> CommandeEnregistreByDay() {
		// TODO Auto-generated method stub
		return null;
	}
	
	@Override
	public List<Statistique> CommandeEnregistreByMonth() {
		// TODO Auto-generated method stub
		return null;
	}
	
	@Override
	public List<Statistique> CommandeEnvoyeByDay() {
		// TODO Auto-generated method stub
		return null;
	}
	
	@Override
	public List<Statistique> CommandeEnvoyeByMonth() throws IPIException {
		List<Statistique> listeStats = new ArrayList<Statistique>();

		try {
			Connection vConnection = jdbcUtils.getConnection();
			Statement vStmt = vConnection.createStatement();

			String vRequeteSelect = "SELECT count(*) as NOMBRE, CREATION_DATE FROM IPII_ORDERS o WHERE o.STATUS = 'sending' AND CREATION_DATE BETWEEN ? AND ? GROUP BY CREATION_DATE ORDER BY CREATION_DATE";
			PreparedStatement vPs = vConnection.prepareStatement(vRequeteSelect);
			
			ServiceDate sDate = new ServiceDate();
			Date dateDebut = sDate.getFirstDateOfMonth();
			vPs.setDate(1, sDate.convertDateToSqlDate(dateDebut));
			Date dateFin = sDate.getLastDateOfMonth();
			vPs.setDate(2, sDate.convertDateToSqlDate(dateFin));
			vPs.execute();
			
			ResultSet vRs = vPs.getResultSet();
			while (vRs.next()) {
				Statistique stat = new Statistique();
				// Date
				
				Date laDate = sDate.convertSqlDateToDate(vRs.getDate("CREATION_DATE"));
				stat.setDate(sDate.convertDateToString(laDate));
				stat.setNombre(vRs.getInt("NOMBRE"));

				listeStats.add(stat);
			}

			vStmt.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("Exception.dao.technique");
		}
		return listeStats;
	}
	
	@Override
	public List<Statistique> StockArticleByMonth(int refArticle) {
		// TODO Auto-generated method stub
		return null;
	}
	
	@Override
	public List<Statistique> StockArticleByYear(int refArticle) {
		// TODO Auto-generated method stub
		return null;
	}
}
